﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Data;
using System.Collections;
using System.Data.OleDb;
using System.Text;

/// <summary>
///ExcelHelper 的摘要说明
/// </summary>
public class ExcelHelper
{
    static string xlsConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
    static string csvConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Text;FMT=Delimited;HDR=YES;\"";
    public ExcelHelper()
    {
        //
        //TODO: 在此处添加构造函数逻辑
        //
    }
    public static DataSet ExcelDataSource(string filepath, string sheetname,bool csv)
    {
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
        OleDbConnection conn = new OleDbConnection();
        if (csv)
        {
            conn.ConnectionString = string.Format(csvConn, filepath);
        }
        else {
            conn.ConnectionString = string.Format(xlsConn, filepath);
        }
        OleDbDataAdapter oada = new OleDbDataAdapter("select * from " + sheetname, strConn);
        DataSet ds = new DataSet();
        oada.Fill(ds);
        conn.Close();
        return ds;
    }

    public static DataTable ExcelData(string filepath, string sheetname)
    {
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
        OleDbConnection conn = new OleDbConnection();

        conn.ConnectionString = string.Format(xlsConn, filepath);

        OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
        DataSet ds = new DataSet();
        oada.Fill(ds);
        conn.Close();
        return ds.Tables[0];
    }
    public static DataSet ExcelDataSource(string filepath, string sheetname)
    {
        return ExcelDataSource(filepath, sheetname, false);
    }

    //获得Excel中的所有sheetname。   
    public static ArrayList ExcelSheetName(string filepath,bool csv)
    {
        ArrayList al = new ArrayList();
        OleDbConnection conn = new OleDbConnection();
        if (csv)
        {
            conn.ConnectionString = string.Format(csvConn, filepath);
        }
        else
        {
            conn.ConnectionString = string.Format(xlsConn, filepath);
        }
        conn.Open();
        DataTable sheetNames = conn.GetOleDbSchemaTable
       (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        conn.Close();
        foreach (DataRow dr in sheetNames.Rows)
        {
            al.Add(dr[2]);
        }
        return al;
    }
    //获得Excel中的所有sheetname。   
    public static ArrayList ExcelSheetName(string filepath)
    {
        return ExcelSheetName(filepath, false);
    }
    public static string CreateExcel(DataTable table)
    {
        StringBuilder tableBuilder = new StringBuilder("<table>");
        int colCount = table.Columns.Count;
        foreach (DataColumn dc in table.Columns)
        {
            tableBuilder.Append("<th>").Append(dc.ColumnName).Append("</th>");
        }
        foreach (DataRow r in table.Rows)
        {
            tableBuilder.Append("<tr>");
            for (int i = 0; i < colCount; i++)
            {
                tableBuilder.Append("<td>").Append(r[i].ToString()).Append("</td>");
            }
            tableBuilder.Append("</tr>");
        }
        return tableBuilder.ToString();
    }
    public static void DownloadAsExcel(DataTable dt, string strFileName)
    {
        try
        {
            StringWriter sw = new StringWriter();
            string colstr = "";
            foreach (DataColumn col in dt.Columns)
            {
                colstr += col.ColumnName + "\t";
            }
            sw.WriteLine(colstr);

            foreach (DataRow row in dt.Rows)
            {
                colstr = "";
                foreach (DataColumn col in dt.Columns)
                {
                    colstr += row[col.ColumnName].ToString() + "\t";
                }
                sw.WriteLine(colstr);
            }
            sw.Close();
            System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileName + "");
            System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
            System.Web.HttpContext.Current.Response.Write(sw);
            System.Web.HttpContext.Current.Response.End();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}
